﻿<?xml version="1.0" encoding="utf-8"?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobjlist of eComSalesOrder(customer(contact),status,orderContact)">
	<Sql>
		SELECT        
			eComSalesOrders.id, eComSalesOrders.createdDate, eComSalesOrders.statusId, eComSalesOrders.seqNumber,
			Users.id AS customerid, Users.userName AS customeruserName,
			Contacts.id AS contactid, Contacts.lastName AS contactlastName, Contacts.firstName AS contactfirstName, Articles.baseTitle AS statusbaseTitle,
			Articles.uniqueTitle AS statusuniqueTitle, Articles.translatedTitles, Articles.id AS statusid,
			Contacts_1.id AS orderContactid, Contacts_1.lastName as orderContactlastName,	Contacts_1.firstName as orderContactfirstName
		FROM
			Contacts AS Contacts_1 RIGHT OUTER JOIN
		eComSalesOrders ON Contacts_1.id = eComSalesOrders.contactId LEFT OUTER JOIN
		Articles ON eComSalesOrders.statusId = Articles.id LEFT OUTER JOIN
		Contacts RIGHT OUTER JOIN
		Users ON Contacts.id = Users.id ON eComSalesOrders.userId = Users.id
		WHERE        (eComSalesOrders.companyId = @companyId) 
			AND Articles.uniqueTitle &lt;&gt; 'ecom_sysStatus_Open'
		<code>
			<![CDATA[
				if(info.get("personalOnly") is bool && (bool)info.get("personalOnly"))
				{
					sql.Append(" AND eComSalesOrders.userId = @customerid ");
					addParameter(cmd,"customer.id","customerid","UniqueIdentifier", info);
				}
			
			
				string firstPerc = "", secondPerc = "", c="x";
				if(criteria!=null){
					IBizObjList list = criteria.get("filterOptions") as IBizObjList;
					if(list!=null && list.numElements > 0)
						sql.Append(" AND ");
					for(int x=0;x<list.numElements;x++)
					{
						c = list[x].get("criteria") as string;
						if (string.Compare(c, "s", true) == 0)
							firstPerc = "%";
						else if (string.Compare(c, "e", true) == 0)
							secondPerc = "%";
						else if (string.Compare(c, "c", true) == 0)
							firstPerc = secondPerc = "%";
						if (firstPerc.Length > 0 || secondPerc.Length > 0)
							c = "like";
									   
						if(list[x].get("field") != null && (string)list[x].get("field") != "")
						{
							sql.AppendFormat(" {0} {1} '{3}{2}{4}'", list[x].get("field"), c, list[x].get("value"), firstPerc, secondPerc);
							if(list.numElements > 1)
								sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND ":"  OR ");
						}
					}
				}
			]]>
	</code>
		ORDER BY createdDate desc
	</Sql>
	<Param name="customer.companyId"  dbName="companyId" type="UniqueIdentifier"/>
</AdoNetDataAccess>
